INNER JOIN과 LEFT JOIN 결과 차이 이해하기

목차

• #문제가 되는 상황
• #샘플 데이터부터 준비한다
• #INNER JOIN은 교집합만 남긴다
• #LEFT JOIN은 왼쪽 행을 보존한다
• #오른쪽 조건을 ON과 WHERE 어디에 둘까
• #없는 관계를 찾는 anti join
• #COUNT 별표와 COUNT 컬럼의 차이
• #1대N JOIN에서 행이 늘어나는 이유
• #여러 LEFT JOIN이 만드는 곱셈
• #NULL을 애플리케이션 타입에 반영한다
• #실전 점검 목록
• #결론
• #관련 노트

문제가 되는 상황

사용자 목록에 공개 프로필이 있으면 nickname을 보여 주고, 없으면 사용자만 표시하려고 LEFT JOIN을 썼는데 프로필 없는 사용자가 사라질 때가 있다. 오른쪽 테이블 조건을 WHERE에 둔 것이 원인일 수 있다.

JOIN을 잘 이해하려면 문법보다 결과 행을 직접 그려 보는 편이 빠르다. 어느 테이블의 행을 반드시 보존해야 하는지, 한 행이 몇 행과 연결되는지, 일치하지 않은 오른쪽 컬럼이 NULL일 때 조건과 집계가 어떻게 동작하는지를 순서대로 본다.

샘플 데이터부터 준비한다

users에는 세 사용자가 있고 profiles에는 두 사용자의 프로필만 있다고 하자.

CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE profiles (
  user_id BIGINT PRIMARY KEY,
  nickname VARCHAR(100) NOT NULL,
  is_public BOOLEAN NOT NULL
);

Ara는 profile이 없다. 이 차이가 INNER와 LEFT 결과를 나눈다.

INNER JOIN은 교집합만 남긴다

SELECT u.id, u.name, p.nickname
FROM users u
INNER JOIN profiles p ON p.user_id = u.id
ORDER BY u.id;

양쪽에 일치하는 1과 2만 결과에 남는다.

프로필이 존재하는 사용자만 조회하려는 의도라면 INNER JOIN이 정확하다. 프로필이 없는 행은 잘못 누락된 것이 아니라 join 조건을 만족하지 않은 것이다.

JOIN만 적으면 일반적으로 INNER JOIN을 뜻하지만 리뷰에서는 관계 의도를 명확하게 하기 위해 팀 스타일에 따라 INNER JOIN을 써도 된다.

LEFT JOIN은 왼쪽 행을 보존한다

SELECT u.id, u.name, p.nickname
FROM users u
LEFT JOIN profiles p ON p.user_id = u.id
ORDER BY u.id;

왼쪽 users 세 행은 모두 남고 일치하지 않은 오른쪽 컬럼은 NULL로 채워진다.

“왼쪽”은 테이블의 물리적 성질이 아니라 query에서 LEFT JOIN 앞에 놓은 결과 집합이다. 복잡한 join에서는 먼저 만들어진 왼쪽 중간 결과를 보존한다.

flowchart LR
    U[users 1,2,3] --> L[LEFT JOIN result 1,2,3]
    P[profiles 1,2] --> L
    U --> I[INNER JOIN result 1,2]
    P --> I

오른쪽 조건을 ON과 WHERE 어디에 둘까

공개 profile만 붙이되 모든 사용자를 유지하고 싶다면 조건을 ON에 둔다.

SELECT u.id, u.name, p.nickname
FROM users u
LEFT JOIN profiles p
  ON p.user_id = u.id
 AND p.is_public = true
ORDER BY u.id;

Joon의 profile은 있지만 공개 조건을 만족하지 않아 오른쪽이 NULL로 채워진다. Ara도 profile이 없으므로 NULL이다.

반면 같은 조건을 WHERE에 두면 join이 끝난 뒤 결과 행을 필터링한다.

SELECT u.id, u.name, p.nickname
FROM users u
LEFT JOIN profiles p ON p.user_id = u.id
WHERE p.is_public = true
ORDER BY u.id;

프로필이 없는 Ara의 p.is_public은 NULL이고 NULL = true는 true가 아니다. Joon은 false다. 결과에는 Mina만 남아 이 조건에서는 사실상 공개 profile이 있는 사용자만 조회하는 INNER JOIN과 비슷해진다.

없는 관계를 찾는 anti join

프로필이 없는 사용자를 찾으려면 LEFT JOIN 후 오른쪽 key가 NULL인 행을 선택할 수 있다.

SELECT u.id, u.name
FROM users u
LEFT JOIN profiles p ON p.user_id = u.id
WHERE p.user_id IS NULL;

오른쪽의 nullable 업무 컬럼 대신 p.user_id처럼 원래 NULL이 될 수 없는 key를 검사한다. nickname 자체가 nullable이면 p.nickname IS NULL은 “프로필이 없음”과 “프로필은 있지만 nickname이 NULL”을 구분하지 못한다.

같은 의도는 NOT EXISTS로 더 직접 표현할 수 있다.

SELECT u.id, u.name
FROM users u
WHERE NOT EXISTS (
  SELECT 1
  FROM profiles p
  WHERE p.user_id = u.id
);

DB optimizer가 비슷한 anti-join 계획으로 만들 수 있으므로 표현의 명확성과 실행 계획을 함께 본다.

COUNT 별표와 COUNT 컬럼의 차이

LEFT JOIN 결과를 group하면 COUNT(*)는 왼쪽 보존 행도 센다.

SELECT
  u.id,
  COUNT(*) AS row_count,
  COUNT(p.user_id) AS profile_count
FROM users u
LEFT JOIN profiles p ON p.user_id = u.id
GROUP BY u.id
ORDER BY u.id;

Ara도 LEFT JOIN 결과 행 하나가 있으므로 COUNT(*) = 1이다. COUNT(expression)은 NULL을 세지 않으므로 COUNT(p.user_id) = 0이다. 관계 수를 세려면 오른쪽의 non-null key를 count한다.

1대N JOIN에서 행이 늘어나는 이유

users와 orders가 1:N이면 사용자 한 명이 주문 수만큼 반복된다.

SELECT u.id, u.name, o.id AS order_id
FROM users u
JOIN orders o ON o.user_id = u.id;

Mina는 두 행이 된다. 이는 DB가 중복을 만든 것이 아니라 관계의 cardinality를 정확히 펼친 것이다.

사용자 수를 세면서 join한다면 COUNT(*)는 주문 행 수를 센다.

SELECT COUNT(DISTINCT u.id) AS user_count
FROM users u
JOIN orders o ON o.user_id = u.id;

하지만 DISTINCT를 습관적으로 붙여 잘못된 join을 숨기지 않는다. 존재 여부만 필요하면 EXISTS, 집계만 필요하면 orders를 먼저 user별로 group한 뒤 join하는 편이 의도를 잘 드러낼 수 있다.

여러 LEFT JOIN이 만드는 곱셈

사용자 한 명에게 주문 2개와 주소 3개가 있는데 둘을 동시에 join하면 최대 6행 조합이 생긴다.

SELECT u.id, o.id, a.id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
LEFT JOIN addresses a ON a.user_id = u.id;

이 결과에서 주문 금액을 SUM하면 주소 수만큼 중복 합산된다. 각 1:N 관계를 먼저 집계한다.

SELECT
  u.id,
  COALESCE(os.order_count, 0) AS order_count,
  COALESCE(ad.address_count, 0) AS address_count
FROM users u
LEFT JOIN (
  SELECT user_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY user_id
) os ON os.user_id = u.id
LEFT JOIN (
  SELECT user_id, COUNT(*) AS address_count
  FROM addresses
  GROUP BY user_id
) ad ON ad.user_id = u.id;

query 작성 전 각 join이 1:1, 1:N, N:M 중 무엇인지 표기하면 예상 행 수를 판단하기 쉽다.

NULL을 애플리케이션 타입에 반영한다

DB의 profiles.nickname이 NOT NULL이어도 LEFT JOIN 결과에서는 오른쪽 전체가 없을 수 있어 NULL이 된다.

type UserWithOptionalProfile = {
  id: string;
  name: string;
  nickname: string | null;
};

ORM과 query builder가 join nullability를 제대로 추론하는지 확인한다. nickname as string으로 단언하면 프로필 없는 사용자에서 런타임 오류가 난다.

JSON 응답에서 profile이 없을 때 profile: null로 보낼지 필드를 생략할지 API 계약을 정한다.

{
  "id": "3",
  "name": "Ara",
  "profile": null
}

실전 점검 목록

INNER JOIN은 양쪽에 일치하는 행만 남기고 LEFT JOIN은 왼쪽 행을 모두 보존한다. 오른쪽 조건을 ON에 둘지 WHERE에 둘지 의도를 구분한다.

결론

INNER JOIN은 양쪽 관계가 일치하는 행만 남기고 LEFT JOIN은 오른쪽이 없어도 왼쪽 행을 NULL과 함께 보존한다. 오른쪽 조건을 ON에 두면 붙일 관계를 제한하고 WHERE에 두면 join 이후 행을 제거한다. NULL 집계와 1:N cardinality, 여러 JOIN의 행 곱셈까지 예상한 뒤 애플리케이션 타입에도 nullable 결과를 반영해야 한다.

관련 노트

• SQL JOIN
• 관계형 데이터베이스의 관계